IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MasterEventLog_MasterEventLogType]') AND parent_object_id = OBJECT_ID(N'[dbo].[MasterEventLog]'))
BEGIN
     ALTER TABLE [dbo].[MasterEventLog] DROP CONSTRAINT [FK_MasterEventLog_MasterEventLogType]
END
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MasterEventLog_SrvrInstnc]') AND parent_object_id = OBJECT_ID(N'[dbo].[MasterEventLog]'))
BEGIN
     ALTER TABLE [dbo].[MasterEventLog] DROP CONSTRAINT [FK_MasterEventLog_SrvrInstnc]
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MasterEventLog]') AND type in (N'U'))
BEGIN
     DROP TABLE [dbo].[MasterEventLog]
END
GO

CREATE TABLE [dbo].[MasterEventLog](
     [SrvrInstncDatabaseId] [int]              NOT NULL,
     [EventLogId]           [bigint]           NOT NULL,
     [RecordGUID]           [uniqueidentifier] NOT NULL,
     [EventLogTypeId]       [int]              NOT NULL,
     [LogDt]                [datetime]         NOT NULL,
     [HostName]             [varchar](128)     NOT NULL,
     [LoginName]            [varchar](128)     NOT NULL,
     [DatabaseUserName]     [varchar](128)         NULL,
     [DatabaseName]         [varchar](128)         NULL,
     [AppName]              [varchar](128)         NULL,
     [SPID]                 [int]              NOT NULL,
     [SID]                  [varbinary](85)        NULL,
     [LogByApp]             [varchar](128)         NULL,
     [LogByAppSubSystem]    [varchar](128)         NULL,
     [LogByDesc]            [varchar](128)         NULL,
     [LogText]              [varchar](6144)        NULL,
     [TraceText]            [varchar](max)         NULL,
     [TraceImage]           [varbinary](max)       NULL,
     [MiscVC1]              [varchar](128)         NULL,
     [MiscVC2]              [varchar](128)         NULL,
     [MiscVC3]              [varchar](128)         NULL,
     [MiscVC4]              [varchar](128)         NULL,
     [MiscInt1]             [int]                  NULL,
     [MiscInt2]             [int]                  NULL,
     [MiscInt3]             [int]                  NULL,
     [MiscInt4]             [int]                  NULL,
     [MiscTinyint1]         [tinyint]              NULL,
     [MiscTinyint2]         [tinyint]              NULL,
     [MiscTinyint3]         [tinyint]              NULL,
     [MiscTinyint4]         [tinyint]              NULL,
     [MiscBigInt1]          [bigint]               NULL,
     [MiscBigInt2]          [bigint]               NULL,
     [MiscBigInt3]          [bigint]               NULL,
     [MiscBigInt4]          [bigint]               NULL,
     [MiscDt1]              [datetime]             NULL,
     [MiscDt2]              [datetime]             NULL,
     [MiscDt3]              [datetime]             NULL,
     [MiscDt4]              [datetime]             NULL,
     [MiscGUID1]            [uniqueidentifier]     NULL,
     [MiscGUID2]            [uniqueidentifier]     NULL,
     [MiscGUID3]            [uniqueidentifier]     NULL,
     [MiscGUID4]            [uniqueidentifier]     NULL,
     [PackageExecutionId]   [bigint]               NULL,
     CONSTRAINT [PK_MasterEventLog] PRIMARY KEY CLUSTERED 
          ( [SrvrInstncDatabaseId] ASC, [EventLogId] ASC )
) TEXTIMAGE_ON Data_BLOB
GO

-- Determine SQL Version to determine which default value will be used for RecordId
-- SQL 2005 does not support newsequentialid() so newid() will be used in 2005, but
-- newsequentialid() will be used for later versions.  We also don't test for 
-- SQL 2000 since there are a number of datatypes in the table above that could not
-- be used in SQL 2000.
DECLARE @PV varchar(30),
        @V  int

SET @PV = CONVERT( varchar(30), SERVERPROPERTY('ProductVersion') )

SELECT @V = SUBSTRING(@PV,1, CHARINDEX('.',@PV) -1 )

IF ( @V = 9 )
BEGIN -- SQL 2005
     EXEC ( 'ALTER TABLE [dbo].[MasterEventLog] ADD CONSTRAINT [DF_MasterEventLog_RecordGUID] DEFAULT (newid()) FOR [RecordGUID]' )
END
ELSE
BEGIN -- SQL 2008 + 
     EXEC ( 'ALTER TABLE [dbo].[MasterEventLog] ADD CONSTRAINT [DF_MasterEventLog_RecordGUID] DEFAULT (newsequentialid()) FOR [RecordGUID]' )
END
GO

CREATE NONCLUSTERED INDEX [IX_MasterEventLog_App_AppSubSystem_Desc] ON [dbo].[MasterEventLog] 
     ( [LogByApp] ASC, [LogByAppSubSystem] ASC, [LogByDesc] ASC )
GO

CREATE NONCLUSTERED INDEX [IX_MasterEventLog_AppName] ON [dbo].[MasterEventLog] 
     ( [AppName] ASC )
GO

CREATE NONCLUSTERED INDEX [IX_MasterEventLog_DatabaseName] ON [dbo].[MasterEventLog] 
     ( [DatabaseName] ASC )
GO

CREATE NONCLUSTERED INDEX [IX_MasterEventLog_EventLogTypeId] ON [dbo].[MasterEventLog] 
     ( [EventLogTypeId] ASC )
GO

CREATE NONCLUSTERED INDEX [IX_MasterEventLog_HostName] ON [dbo].[MasterEventLog] 
     ( [HostName] ASC )
GO

CREATE NONCLUSTERED INDEX [IX_MasterEventLog_LogDt] ON [dbo].[MasterEventLog] 
     ( [LogDt] ASC )
GO

CREATE NONCLUSTERED INDEX [IX_MasterEventLog_LoginName] ON [dbo].[MasterEventLog] 
     ( [LoginName] ASC )
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_MasterEventLog_RecordGUID] ON [dbo].[MasterEventLog] 
     ( [RecordGUID] ASC )
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Database on a Database Server Instance.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'SrvrInstncDatabaseId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Event Log entry.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'EventLogId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier of this particular logged entry.  This is used to uniquely identify this record without a composite key, this can be used for a FT index key.  Is a newid in SQL 2005, and a newsequentialid in SQL 2008.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'RecordGUID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Event Log Type.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'EventLogTypeId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date/time when event was logged.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'LogDt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Host Name of logged activity.  Caution!  Is not set by system therefore can be spoofed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'HostName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server login name of logged event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'LoginName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Username in the database of logged event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'DatabaseUserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database name where activity was logged into.  For single application use will usually match database of application, for server level use will usually match database of calling application.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'DatabaseName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Application Name of logged activity.  Caution!  Is not set by system therefore can be spoofed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'AppName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server Process Id of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'SPID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SID of user of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'SID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'LogByApp'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application Sub-System.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'LogByAppSubSystem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application Description, usually additional additional detail such as sub-sub-process, or internal identifier.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'LogByDesc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Log message of the logged activity.  6K Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'LogText'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Traced text of logged activity.  2GB Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'TraceText'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Traced binary image of logged activity.  2GB Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'TraceImage'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscVC1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscVC2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscVC3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscVC4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscInt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscInt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscInt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscInt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscDt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscDt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscDt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscDt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Execution Id of the package that populated or updated this row (usually by SSIS).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog', @level2type=N'COLUMN',@level2name=N'PackageExecutionId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Combined event logging.  Contains combined EventLog tables from one or more server instances and databases.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MasterEventLog'
GO
